

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;


public class queryDB extends HttpServlet {

	protected void doGet(HttpServletRequest req, HttpServletResponse rsp) 
	throws ServletException, IOException 
	{
		rsp.setContentType("text/html");
		PrintWriter out = rsp.getWriter();
		
		String function = req.getParameter("function");
		
		try
		{
			Statement stmt;
		    Class.forName("com.mysql.jdbc.Driver");
		    String url = "jdbc:mysql://localhost:3306/ideas_i";
		    Connection con = DriverManager.getConnection(url, "root", "ideas_inventory");
	
		    //defines the stmt variable as a statement of the current connection
		    stmt = con.createStatement();
	    
			if(function.equals("workstations"))
			{
				String inName = req.getParameter("name");
				ResultSet info = stmt.executeQuery("SELECT * FROM people WHERE workstation=\"" + inName + "\";");
				if (info != null)
				{
					String qryStmt1 = "SELECT name, period_inv, key_tasks FROM people ";
					String qryStmt2 = "WHERE workstation=\"" + inName + "\";";
					
					out.println("<tr>");
	        		out.println("<th class=\"records\">Individual's Name</th>");
	        		out.println("<th class=\"records\">Period of Involvment</th>");
	        		out.println("<th class=\"records\">Key tasks and responsibilities</th>");
	        		out.println("</tr>");
	        		
	        		ResultSet result = stmt.executeQuery(qryStmt1 + qryStmt2);
	        		
	        		while (result.next())
	        		{
	        			out.println("<tr>");
	        			out.println("<td class=\"records\">" + result.getString(1) + "</td>");
	        			out.println("<td class=\"records\">" + result.getString(2) + "</td>");
	        			out.println("<td class=\"records\">" + result.getString(3) + "</td>");
	        			out.println("</tr>");
	        		}
					
				}
				else
				{
					out.println("<tr><td>There are no matches for that workstation</td></tr>");
				}
			}
			else
	    	{
	    		//returns info about the queried name, currently unused except in checking
	    		String inName = req.getParameter("name");
	    		ResultSet info = stmt.executeQuery("SELECT * FROM people WHERE name=\"" + inName + "\";");
	    		//checks if the name actually exists in the people table
	    		if(info != null)
	    		{
	        		//constructs a reusable SQL statement string for all tables
	        		String qryStmt2 = " WHERE owner_name=\"" + inName + "\";";
	        		
	        		out.println("<tr>");
	        		out.println("<th class=\"records\">Item Type</th>");
	        		out.println("<th class=\"records\">Item Name</th>");
	        		out.println("</tr>");
	        		
	        		
	        		//queries the software table
	        		
	        		ResultSet resSoft = stmt.executeQuery("SELECT soft_name FROM software" + qryStmt2);
	        		
	        		//prints results of the query in table form
	        		while(resSoft.next())
	        		{
	        			out.println("<tr>");
	        			out.println("<td class=\"records\">Software</td>");
	        			out.println("<td class=\"records\">" + resSoft.getString(1) + "</td>");
	        			out.println("</tr>");
	        		}
	        		
	        		//queries the books table
	        		ResultSet resBook = stmt.executeQuery("SELECT product_name FROM books" + qryStmt2);
	        		
	        		//prints results of the query in table form
	        		while(resBook.next())
	        		{
	        			out.println("<tr>");
	        			out.println("<td class=\"records\">Book</td>");
	        			out.println("<td class=\"records\">" + resBook.getString(1) + "</td>");
	        			out.println("</tr>");
	        		}
	        	}
	    		//otherwise indicate that the name does not match any item entries
	    		else
	    		{
	    		    out.println("<tr><td>There are no matches for that name</td></tr>");
	    		}
	    	}
    	}
    	catch (Exception e)
    	{
    		out.println("<tr><td>Exception: " + e + "</td></tr>");
    	}
	}
}
